from django.core.paginator import Paginator
from django.db import transaction
from django.db.models import Q
from django.shortcuts import render
from django.http import HttpResponse
from openpyxl.workbook import Workbook
from course.models import Course
from student.models import Student
from utils.customerutils import CustomerUtils
from utils.responsestatus import ResponseStatus
from clazz.models import Clazz
from utils import responsedata
import pandas as pd
from django.db.utils import IntegrityError


# Create your views here.
def student_page(request):
    """
    学生信息分页    搜索    班级名  学生名
     1.  根据班级名称查询    班级id
     2.  学生名 课直接作为条件使用
    :param request:
    :return:
    """
    if request.method == 'POST':
        # {page:1,size:5，username,email,active}
        form_data = CustomerUtils.get_request_data(request)
        condition = Q()
        # 动态条件
        if form_data.get('class_name').strip():
            clazz = Clazz.objects.filter(class_name=form_data.get('class_name')).first()
            if clazz:
                # TODO class_id  -> clazz_id
                condition &= Q(clazz_id=clazz.id)
            else:
                condition &= Q(clazz_id=None)
        if form_data.get('student_name').strip():
            condition &= Q(student_name__icontains=form_data['student_name'])

        # 分页处理  total  tableData
        student_list = Student.objects.filter(condition).order_by('-id')
        # 获取分页对象
        paginator = Paginator(student_list, form_data.get('size', 5))
        # 获取数据总条数
        total = paginator.count
        # 当前页对象
        current_page = paginator.page(form_data.get('page', 1))
        # 当前页数据   [{},{},{}]
        table_data = current_page.object_list
        students = []
        for student in table_data:
            students.append({
                'id': student.id,
                'student_name': student.student_name,
                'student_no': student.student_no,
                'student_age': student.student_age,
                'student_sex': '男' if student.student_gender == 1 else '女',
                'student_tel': student.student_tel,
                'student_state': student.student_state,
                'school_time': student.school_time.strftime('%Y-%m-%d'),
                'class_name': student.clazz.class_name,
                'course_name': student.course.course_name
            })

        response = {
            'code': ResponseStatus.BUSINESS_SUCCESS,
            'data': {
                'total': total,
                'tableData': students
            }
        }

        return CustomerUtils.response_json(response)


def student_one(request, id):
    student = Student.objects.filter(id=id).values().first()
    response = {
        'code': ResponseStatus.BUSINESS_SUCCESS,
        'data': {
            'student': student
        }
    }
    return CustomerUtils.response_json(response)


def student_insert_edit(request):
    if request.method == 'POST':
        form_data = CustomerUtils.get_request_data(request)
        print('----------------------------', form_data)
        student_id = form_data.get('id')
        from django.db import IntegrityError
        try:
            if student_id is not None:
                # 更新
                Student.objects.filter(id=student_id).update(**form_data)
            else:
                Student(**form_data).save()
        except IntegrityError as e:
            response = responsedata.BUSINESS_ERROR_DATA
            response['message'] = str(e)
            return CustomerUtils.response_json(response)
        else:
            return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)


def student_remove(request, id):
    Student.objects.filter(id=id).delete()
    return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)


def student_state(request):
    if request.method == 'POST':
        form_data = CustomerUtils.get_request_data(request)
        Student.objects.filter(id=form_data.get('id')).update(student_state=form_data.get('state'))
        return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)


# 当前视图中的多条sql语句被添加在一个事务中
@transaction.atomic
def import_excel(request):
    """
    上传excel文件   解析excel  pandas   read_excel(file)  rows   ->  models -> save
    事务:
       执行多条sql语句时为了保证多条sql语句全部成功或者全部失败   可以将sql语句加入在一个事务环境下
       事务可以控制多条sql语句的提交和回滚操作 从而保证sql语句全部成功或失败
    事务的特点:
        原子性   一致性  隔离性  持久性
    事务的隔离级别
        读未提交:
        读已提交
        可重复读  mysql
        串行化
    :param request:   file
    :return:
    """
    if request.method == 'POST':
        # 获取上传的excel文件
        file = request.FILES['file']
        df = pd.read_excel(file)
        try:
            for index, row in df.iterrows():
                # 根据课程名字 班级名字  查询课程id  班级id    AttributeError
                clazz_id = Clazz.objects.filter(class_name=row['班级']).first().id
                course_id = Course.objects.filter(course_name=row['课程']).first().id
                # django.db.utils.IntegrityError
                student = Student(
                    student_name=row['姓名'],
                    student_no=row['学号'],
                    student_age=row['年龄'],
                    student_tel=row['联系方式'],
                    student_state=1 if row['状态'] == '在学' else 2,
                    clazz_id=clazz_id,
                    course_id=course_id,
                    student_gender=1 if row['性别'] == '男' else 2,
                    school_time=row['入学时间']
                )
                student.save()
        except AttributeError as e:
            response = responsedata.BUSINESS_ERROR_DATA
            response['message'] = '班级或课程名字不存在'
            return CustomerUtils.response_json(response)
        except IntegrityError as e:
            response = responsedata.BUSINESS_ERROR_DATA
            response['message'] = str(e)
            return CustomerUtils.response_json(response)
        else:
            return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)


def export_excel(request):
    """
        设置以附件形式响应
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename="student.xlsx"'
    :param request:
    :return:
    """
    # 创建excel工作簿
    work_book = Workbook()
    # 获取工作簿中的一张表
    sheet = work_book.active
    # 设置表名
    sheet.title = '学生信息表'
    # 表头
    header = ['学号', '姓名', '年龄', '性别', '联系方式', '课程', '班级', '状态', '入学时间']
    # 追加表头行
    sheet.append(header)
    # 表格数据
    condition = Q()
    # 动态条件
    class_name = request.GET.get('class_name')
    if class_name.strip():
        clazz = Clazz.objects.filter(class_name=class_name).first()
        if clazz:
            # TODO class_id  -> clazz_id
            condition &= Q(clazz_id=clazz.id)
        else:
            condition &= Q(clazz_id=None)
    student_name = request.GET.get('student_name')
    if student_name.strip():
        condition &= Q(student_name__icontains=student_name)

    students = Student.objects.filter(condition).order_by('-id')
    #  遍历学生对象  ->  添加到excel表中
    for student in students:
        # 性别   状态
        student_sex = '男' if student.student_gender == 1 else '女'
        student_states = '在学' if student.student_state == 1 else '休学'
        # 课程  班级
        class_name = Clazz.objects.filter(id=student.clazz_id).first().class_name
        course_name = Course.objects.filter(id=student.course_id).first().course_name
        row = [
            student.student_no,
            student.student_name,
            student.student_age,
            student_sex,
            student.student_tel,
            course_name,
            class_name,
            student_states,
            student.school_time.strftime('%Y-%m-%d'),
        ]
        sheet.append(row)

    # 设置响应对象  文件形式
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    # 将excel以附件的形式进行响应  attachment 附件 浏览器不需要打开    filename  文件下载后的名字
    response['Content-Disposition'] = 'attachment; filename="students.xlsx"'
    # 将工作簿和response对象绑定
    work_book.save(response)
    return response


from django.db import connection


# [{},{}]
def student_pie_data(request):
    cursor = connection.cursor()
    str_sql = """
                SELECT  course.course_name  AS `name`,COUNT(student.id) AS  `value`
                FROM student 
                LEFT JOIN course  ON  student.course_id = course.id
                GROUP BY student.course_id
                """
    cursor.execute(str_sql)
    pie_list = []
    # ((),())
    pie_datas = cursor.fetchall()
    for data in pie_datas:
        pie_list.append({"name": data[0], "value": data[1]})

    response = {
        'code': ResponseStatus.BUSINESS_SUCCESS,
        "data": {
            'pieData': pie_list,
        },
    }
    return CustomerUtils.response_json(response)
